package schronisko;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import Users.Client;

public class Queries {
	
	Query q = new Query();
	ResultSet r=null;
	String query = null;
	
	public int InsertClient(Client c) {
		int rows = 0;
		query = c.getID() +",'"+c.getName() +"','" + c.getSurname() +"','" + c.getCity() +
				"','" + c.getPost() + "','" + c.getHouse() + "','" + c.getPESEL() + 
				"','" + c.getID_card() + "'," + 0;
		try {
			rows = q.Insert("client",query);
			if(rows != 0) {
				return 1;
			}
		} catch (SQLException e) {
			return 0;
		}
		return 0;
	}
	
	public int InsertLoginData(int ID, String username, String pass, String email){
		query = ID + ",'" + username + "','" + pass + "','" + email + "'," + 0 + ", CURRENT_DATE()";
		try {
			int rows = q.Insert("login_data", query);
			if(rows ==1) {
				return 1;
			}
			else {
				return 0;
			}
		} catch (SQLException e) {
			return -1;
		}
	}
	
	public int InsertMessage(int ID_client, String topic, String msg, int viewed){
		query = "null ," + ID_client + ",'" + topic +"','" + msg + "'," + viewed + ", CURRENT_DATE()" ;
		try {
			int rows = q.Insert("message", query);
			if(rows == 1){
				return 1;
			}
			else {
				return 0;
			}
		} catch (SQLException e) {
			return -1;
		}
		
	}

	public int GetUserID(String username) {
		int ID = -1;
		String fields = "*";
		String where = "username";
		String table ="login_data";
		String value = username;
		try {
			r = q.Select(fields, table, where, value);
			if(r.next())
			ID = r.getInt(1);
		} catch (SQLException e) {
			return -1;
		}
		return ID;
	}
	
	
	public int Promote(int ID, int type) {
		int success = 0;
		try {
			r = q.Select("*", "client", "ID_client", ID);
		} catch (SQLException e) {
			System.err.println("Nie udalo sie pobrac ID(promote)");
		}
		try {
			if(r.next()) {
				r.updateInt("type", type);
				r.updateRow();
				success = 1;
			}
		} catch (SQLException e) {
			System.err.println("Nie udalo sie awansowac");
		}
		return success;
	}
	public int getNextID() {
		int ID = 0;
		
		try {
			r = q.Select("max(ID_client)", "login_data", "1", "1");
			if(r.next())
			ID  = r.getInt(1);
		} catch (SQLException e) {
			MySQLConnection.Close();
			return -1;
		}
		ID += 1;
		return ID;
	}
	
	public int updateClient(Client c) {
		try {
			r = q.Select("*", "client", "ID_client",c.getID());
			r.absolute(1);
			if(!c.getName().equals(""))
			r.updateString("name", c.getName());
			if(!c.getSurname().equals(""))
			r.updateString("surname", c.getSurname());
			if(!c.getPost().equals(""))
			r.updateString("post", c.getPost());
			if(!c.getCity().equals(""))
			r.updateString("city", c.getCity());
			if(!c.getHouse().equals(""))
			r.updateString("house", c.getHouse());
			if(c.getPESEL() != null)
			r.updateString("PESEL",c.getPESEL());
			if(!c.getID_card().equals(""))
			r.updateString("ID_card", c.getID_card());
			r.updateRow();
		} catch (SQLException e) {
			return -1;
		}
		return 0;
	}
	public int InsertToken(String token, int ID, String type){
		query = "null, " + ID + ", '"+ token + "', '" + type+"'";
		try {
			q.Insert("token", query);
		} catch (SQLException e) {
			MySQLConnection.Close();
			return -1;
		}
		return 0;
	}
	
	public int ActivateClient(String token, String type){
		
		ResultSet r1 = null;
		int ID = -1;
		
		try {
			r = q.Select_AND("*", "token", "token", token, "type", type);
			if(r.next()){
				ID = r.getInt(2);
			}
			else return -1;
			r1 = q.Select("*", "login_data", "ID_client", ID);
			r1.absolute(1);
			r1.updateInt("active", 1);
			r1.updateRow();
		} catch (SQLException e) {
			return -1;
		}
		
		return 0;
	}


	
	public int ValidateClient(String username, String password) {
		int ID = 0;
		try {
			r = q.Select("*", "login_data", "username", username);
			
			//r.absolute(1);
			if(r.next()){
				ID = r.getInt(1);
				String pass = r.getString(3);
				int active = r.getInt(5);
				if(!pass.equals(password)) {
					return -2; // Bledne haslo
				}
				if(active == 0) {
					return -3; // Konto nieaktywne
				}
			}
			else return -1; // Nie znaleziono uzytkownika w bazie
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return ID; // Logowanie poprawne
	}
	public int getPrivilidge(int ID) {
		int type = 0;
		try {
			r =  q.Select("*", "client", "ID_client", ID);
			if(r.next())
			type = r.getInt("type");
		} catch (SQLException e) {
			MySQLConnection.Close();
			return -1; // blad pobrania praw
		}
		return type; 
	}
	
	public int checkUser(String username){
		try {
			r = q.Select("*", "login_data", "username", username);
			if(r.next()){
				return 1; // znaleziono uzytkownika o podaenej nazwie
			}
			else return 0; // nie znaleziono
			
		} catch (SQLException e) {
			return -1; // bląd
		}
	}
	public int checkEmail(String email){
		try {
			r = q.Select("*", "login_data", "email", email);
			if(r.next()){
				return 1; // znaleziono uzytkownika o podaenym mailu
			}
			else return 0; // nie znaleziono
			
		} catch (SQLException e) {
			return -1; // bląd
		}
	}
	
	public int change_pass(String old_pass, String new_pass, String new_pass1, int ID){
		try {
			r = q.Select("*", "login_data", "ID_client", ID);
			//r.absolute(1);
			if(r.next()){
				if(!old_pass.equals(r.getString("password"))) {
					return -2; // hasla sie nie zgadzaja
				}
				else {
					r.updateString("password", new_pass);
					r.updateRow();
					return 0; // haslo zmienione
				}
			}
			return -1; // problem z baza
		} catch (SQLException e) {
			return -1; // problem z baza
		}
	}
	
	public Client getClient(int ID) {
		Client c;
		try {
			r = q.Select("*", "client", "ID_client", ID);
			if(r.next()){
				c = new Client(ID, r.getString("name"), r.getString("surname"), r.getString("city"), r.getString("post"), r.getString("house"), r.getString("PESEL"), r.getString("ID_card"), "null", "null", "null");
			}
			else return new Client(-1); // problem z baza
		} catch (SQLException e) {
			return new Client(-1); // problem z baza
		}
		return c;
	}
	
	public Client getClient(String username){
		Client c;
		try {
			r = q.Select("*", "login_data natural join client", "username", username);
			if(r.next()){
				c = new Client(r.getInt("ID_client"), r.getString("name"), r.getString("surname"), r.getString("city"), r.getString("post"), r.getString("house"), r.getString("PESEL"), r.getString("ID_card"), r.getString("username"), "UKRYTE", r.getString("email"));
			}
			else return new Client(-1); // problem z baza
		} catch (SQLException e) {
			return new Client(-1); // problem z baza
		}
		return c;
	}
	
	public Client getClient1(String username){
		Client c;
		try {
			r = q.Select_AND("*", "login_data natural join client", "username", username,"type",0);
			if(r.next()){
				c = new Client(r.getInt("ID_client"), r.getString("name"), r.getString("surname"), r.getString("city"), r.getString("post"), r.getString("house"), r.getString("PESEL"), r.getString("ID_card"), r.getString("username"), "UKRYTE", r.getString("email"));
			}
			else return null; // problem z baza
		} catch (SQLException e) {
			return null; // problem z baza
		}
		return c;
	}
	
	public ResultSet getAnimals(){
		try {
			r = q.Select("*", "animal natural join picture","1","1");
		} catch (SQLException e) {
			return null; // problem z baza
		}
		return r;
	}
	
	
	public ResultSet latestAnimals(){
		try {
			r = q.Select2("*","animal natural join picture","(ROUND((DATEDIFF(CURDATE(),add_date))/30.5))");
		} catch (SQLException e) {
			return null; // problem z baza
		}
		return r;		
	}
	
	public ResultSet searchAnimal(String race, String gender, String date_of_birth, String eq, String accessibility){
		try {
			if(accessibility==""){
				if(race==""){
					if(gender==null){
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "1", "1", "1", "1", "1", "1","=","1","1");
						}
						else r=q.Select_3AND("*", "animal natural join picture", "1", "1", "1", "1", "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"1","1");
					}
					else{
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal  natural join picture", "1", "1", "gender", gender, "1", "1", "=","1","1");
						}
						else r=q.Select_3AND("*", "animal", "1", "1", "gender", gender, "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"1","1");
					}
				}
				else {
					if(gender==null){
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "race", race, "1", "1", "1", "1", "=","1","1");
						}
						else r=q.Select_3AND("*", "animal natural join picture", "race", race, "1", "1", "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"1","1");
					}
					else{
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "race", race, "gender", gender, "1", "1", "=","1","1");
						}
						else r=q.Select_3AND("*", "animal natural join picture", "race", race, "gender", gender, "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"1","1");
					}
				}
			}
			else {
				if(race==""){
					if(gender==null){
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "1", "1", "1", "1", "1", "1","=","accessibility",accessibility);
						}
						else r=q.Select_3AND("*", "animal natural join picture", "1", "1", "1", "1", "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"accessibility",accessibility);
					}
					else{
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "1", "1", "gender", gender, "1", "1", "=","accessibility",accessibility);
						}
						else r=q.Select_3AND("*", "animal natural join picture", "1", "1", "gender", gender, "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"accessibility",accessibility);
					}
				}
				else {
					if(gender==null){
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "race", race, "1", "1", "1", "1", "=","accessibility",accessibility);
						}
						else r=q.Select_3AND("*", "animal natural join picture", "race", race, "1", "1", "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"accessibility",accessibility);
					}
					else{
						if(date_of_birth==""){
							r=q.Select_3AND("*", "animal natural join picture", "race", race, "gender", gender, "1", "1", "=","accessibility",accessibility);
						}
						else r=q.Select_3AND("*", "animal natural join picture", "race", race, "gender", gender, "(ROUND((DATEDIFF(CURDATE(),date_of_birth))/30.5))", date_of_birth, eq,"accessibility",accessibility);
					}
				}
			}
			
		} catch (SQLException e) {
			return null; // problem z baza
		}
		return r;
	}
	public int balanseUpdate(String worker, float amount, String title) {
		int rows;
		String query="null, '"+ worker + "', " + amount + ", '"+ title +"', CURDATE()";
		try {
			rows = q.Insert("transactions", query);
		} catch (SQLException e) {
			return -1;
		}
		return rows;
	}
	
	public int changeBalanse(float amount){
		try {
			q.Update("balanse", "balanse", "balanse+"+amount);
		} catch (SQLException e) {
			return 0;
		}
		return 1;
	}
	
	public float getBalanse() {
		float balanse = 0;
		try {
			r = q.Select("*", "balanse", "1", "1");
			if(r.next()){
				balanse = r.getFloat(1);
			}
		} catch (SQLException e) {
			return -1;
		}
		return balanse;
	}
	
	public String[] getWorkers(){
		int count=0;
		try {
			r = q.Select_2OR("count(ID_client)", "client", "type", 1, "type", 2);
			if(r.next())
			count = r.getInt(1);
		} catch (SQLException e1) {
			count = 0;
		}
		
		/* Pobiera liste pracownikow (workers i admins)*/
		
		String[] workers = new String[count*2];
		int index = 0;
		try {
			r=q.Select_2OR("*", "client", "type", 1, "type",2);
			while(r.next()){
				workers[index] = r.getString("name");
				workers[index+1] = r.getString("surname");
				index+=2;
				
			}
		} catch (SQLException e) {
			return null;
		}
		return workers;
	}
	
	public String[] getDetailedWorkers(){
		int count=0;
		try {
			r = q.Select("count(ID_client)", "client", "type !", 0);
			if(r.next())
			count = r.getInt(1);
		} catch (SQLException e1) {
			count = 0;
		}
		
		String[] clients = new String[count*3];
		int index = 0;
		try {
			r=q.Select("*", "client", "type !", 0);
			while(r.next()){
				clients[index] = r.getString("name");
				clients[index+1] = r.getString("surname");
				clients[index+2] = String.valueOf(r.getInt("type"));
				index+=3;
				
			}
		} catch (SQLException e) {
			return null;
		}
		return clients;
	}
	
	public int getID(String name, String surname, String pesel) {
		int ID = -1;
		try {
			r = q.Select_3AND("*", "client", "name", name, "surname", surname,"PESEL",pesel,"=","1",1);
			if(r.next()) {
				ID = r.getInt(1);
			}
		} catch (SQLException e) {
			return -1;
		}
		return ID;
	}
	public ResultSet getTransactions(String month_start, String month_end){        
        if (month_end == "") {        
            month_end = "CURDATE()";        
        }        
        if(month_start == "") {        
            month_start = "1930-01-01";        
        }        
        try {        
            r = q.SelectBetween("*", "transactions", "date", month_start, month_end);        
        } catch (SQLException e) {        
            return null;        
        }        

        return r;        
    }
	
	public int deleteMessage(int ID) {
		try {
			r = q.Select("*","message", "ID_msg", ID);
			if(r.next()){
				r.deleteRow();
			}
		} catch (SQLException e) {
			return -1;
		}
		return 0;
	}
	
	public void viewMessage(int ID, int view){
		try {
			r = q.Select("*", "message", "ID_msg", ID);
			if(r.next()){
				r.updateInt("viewed", view);
				r.updateRow();
			}
		} catch (SQLException e) {
			return;
		}
	}
	
	public int getCountMsg() {
		int count = 0;
		try {
			r = q.Select("COUNT('ID_msg')", "message", "viewed", 0);
			if(r.next()) {
				count = r.getInt(1);
			}
		} catch (SQLException e) {
			return -1;
		}
		return count;
	}
	public ResultSet getMessages(int view) {
		try {
			r = q.Select("*", "message natural join login_data", "viewed", view);
		} catch (SQLException e) {
			return null;
		}
		return r;
	}
	
	public int getDiffAnimal(int ID) {
		int count = 0;
		try {
			r = q.Select("datediff(curdate(),add_date)", "animal", "ID_animal", ID);
			if(r.next())
				count = r.getInt(1);
		} catch (SQLException e) {
			return -1;
		}
		return count;
	}
	
	public int getBook(int ID) {
		int count = 0;
		try {
			r = q.Select("COUNT(ID)", "book", "ID_client", ID);
			if(r.next()) {
				count = r.getInt(1);
			}
		} catch (SQLException e) {
			return -1;
		}
		return count;
	}
	
	public Date getDateOfBook(int ID) {
		Date count = null;
		try {
			r = q.Select("date", "book", "ID_client", ID);
			if(r.next()) {
				count = r.getDate(1);
			}
		} catch (SQLException e) {
			return null;
		}
		return count;
	}
	
	public int Book(int ID){
		int rows = 0;
		query = "null, " + ID + ", CURDATE()";
		try {
			rows = q.Insert("book", query);
		} catch (SQLException e) {
			return -1;
		}
		return rows;
	}
	public ResultSet getUsersAndAdded(){
		try {
			r = q.SelectGroupedBy("username, ID_client", "book natural join login_data", "1", "1","username");
		} catch (SQLException e) {
			
		}
		return r;
	}
	public void deleteBook(int ID) {
		try {
			r = q.Select("*","book", "ID_client", ID);
			if(r.next()){
				r.deleteRow();
			}
		} catch (SQLException e) {
			return;
		}
	}
	public ResultSet select(String query) {
		try {
			r = MySQLConnection.connect().executeQuery(query);
		} catch (SQLException e) {
			return null;
		}
		return r;
	}
	public int insert(String query){
		int rows = 0;
		try {
			rows = MySQLConnection.connect().executeUpdate(query);
		} catch (SQLException e) {
			return -1;
		}
		return rows;
	}
	public int cleanBase(){
		int ID = -1;
		try {
			r = q.Select_2AND_Less("ID_client", "login_data", "DateDiff(CURDATE(),RegisterDate)/7","active",0);
			if(r.next()){
				ID = r.getInt(1);
			}
		

			r = q.Select("*", "token", "ID_client", ID);
			while(r.next()){
				r.deleteRow();
			}

		

			r = q.Select("*", "client", "ID_client", ID);
			while(r.next()){
				r.deleteRow();
			}

		

			r = q.Select("*", "login_data", "ID_client", ID);
			while(r.next()){
				r.deleteRow();
			}

		

			r = q.Select_2AND_Less("*", "book", "(DateDiff(CURDATE(),date)/7)","1",1);
			while(r.next()){
				r.deleteRow();
			}
		} catch (SQLException e) {
			return 0;
		}
		
		return 1;
	}
	
	public int deleteAnimal(int ID) {
		try {
			r = q.Select("*", "picture", "ID_animal", ID);
			if(r.next()) {
				r.deleteRow();
			}
			
			r = q.Select("*", "animal", "ID_animal", ID);
			if(r.next()) {
				r.deleteRow();
			}
		} catch (SQLException e) {
			return 0;
		}
		return 1;
	}
	
	public int deleteUser(int ID) {
		try {
			r = q.Select("*", "token", "ID_client", ID);
			if(r.next()) {
				r.deleteRow();
			}
			
			r = q.Select("*", "client", "ID_client", ID);
			if(r.next()) {
				r.deleteRow();
			}
			
			r = q.Select("*", "login_data", "ID_client", ID);
			if(r.next()) {
				r.deleteRow();
			}
		} catch (SQLException e) {
			return 0;
		}
		return 1;
	}
	
	public int[] getIDAnimal(String name){
		int[] animals;
		try {
			r = q.Select("*", "animal", "name", name);
			r.last();
			int count = r.getRow();
			animals = new int[count];
			r.beforeFirst();
			while(r.next()){
				animals[r.getRow()-1] = r.getInt(1);
			}
		} catch (SQLException e) {
			return null;
		}
		return animals;
	}
	
	public ResultSet getAnimal(int ID){
		try {
			r = q.Select("*", "animal natural join picture", "ID_animal", ID);
		} catch (SQLException e) {
			return null;
		}
		return r;
	}
	
	public int updateAnimal(Animal a) {
		try {
			r = q.Select("*", "animal", "ID_animal",a.getID());
			if(r.next()){
				if(!a.getName().equals(""))
					r.updateString("name", a.getName());
				if(!a.getGender().equals(""))
					r.updateString("gender", a.getGender());
				if(!a.getRace().equals(""))
					r.updateString("race", a.getRace());
				if(!a.getDate().equals(""))
					r.updateDate("date_of_birth", Date.valueOf(a.getDate()));
				if(!a.getAccessibility().equals(""))
					r.updateString("accessibility", a.getAccessibility());
				if(!a.getLastInoculation().equals(""))
					r.updateDate("last_inoculation",Date.valueOf(a.getLastInoculation()));
				if(!a.getDescription().equals(""))
					r.updateString("description", a.getDescription());
				r.updateRow();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
			
		}
		return 1;
	}
	public int makeNote(int ID_client,String text, int note){
		int rows = 0;
		String query ="NULL, "+ID_client+", "+note+", '"+text+"', CURDATE()";
		try {
			rows = q.Insert("notes", query);
		} catch (SQLException e) {
			return -1;
		}
		return rows;
	}
	
	public boolean ifNoted(int ID){
		try {
			r = q.Select("*", "notes", "ID_client", ID);
			if(r.next()){
				return true;
			}
		} catch (SQLException e) {
			return true;
		}
		return false;
	}
	
	public ResultSet getNotes(){
		try {
			r = q.Select("*", "notes natural join client", "1", "1");
		} catch (SQLException e) {
			return null;
		}
		return r;
	}
	
	public float getMeanNote(){
		float mean = 0;
		try {
			r = q.Select("SUM(note)/COUNT(note)", "notes", "1", "1");
			mean = r.getFloat(1);
		} catch (SQLException e) {
			return 0;
		}
		return mean;
	}
}
